package dao;

import entity.Collection;
import entity.Comment;
import entity.Product;
import org.hibernate.*;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import relationEntity.ProductsDetails;
import relationEntity.Products;
import java.util.List;


/**
 * Created by jifeng on 2016/12/12 0012.
 */
public class ProductDao {

    private SessionFactory sessionFactory;

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }
    public SessionFactory getSessionFactory() {
        return sessionFactory;
    }

    private Session getCurrentSession() {
        return sessionFactory.getCurrentSession();
    }

    public void addProduct(Product product){
        getCurrentSession().save(product);
    }

    /**
     * 分页查询
     * @param hql 查询的条件
     * @param offset 开始记录
     * @param length 一次查询几条记录
     * @return 返回查询记录集合
     */
    public List<Products> findProductList(int offset,String categoryName,String subCategoryName,String colorSizeName,String sell,String productName, int length){
        List<Products> productList = null;
        try {
            String  sql="SELECT p.id,p.productName,p.nowPrice,p.sell,c.categoryName,su.subCategoryName,si.colorSizeName,s.stocks";
            String exceptSelectSql =" FROM Product AS p\n" +
                    "LEFT JOIN category AS c ON c.id =p.category\n" +
                    "LEFT JOIN subcategory AS su ON su.id = p.subCategory\n" +
                    "LEFT JOIN colorsize AS si ON si.id =p.colorSizeId\n" +
                    "LEFT JOIN stock AS s ON s.productId = p.id\n" +
                    "where p.deletes=0 ";
            if (categoryName !=null & categoryName !=""){
                exceptSelectSql+=" AND c.categoryName = '"+categoryName+"'";
            }
            if (subCategoryName !=null&subCategoryName!=""){
                exceptSelectSql+=" AND su.subCategoryName = '"+subCategoryName+"'";
            }
            if (colorSizeName !=null &colorSizeName!=""){
                exceptSelectSql+=" AND si.colorSizeName = '"+colorSizeName+"'";
            }
            if (sell != null&sell!=""){
                int s = Integer.valueOf(sell);
                exceptSelectSql+=" AND p.sell = '"+ s +"'";
            }
            if (productName!=null & productName!=""){
                exceptSelectSql+=" AND p.productName LIKE '%" + productName + "%'";
            }
            SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
            query.addScalar("id", StandardBasicTypes.INTEGER);
            query.addScalar("productName", StandardBasicTypes.STRING);
            query.addScalar("nowPrice", StandardBasicTypes.LONG);
            query.addScalar("sell", StandardBasicTypes.INTEGER);
            query.addScalar("categoryName", StandardBasicTypes.STRING);
            query.addScalar("colorSizeName", StandardBasicTypes.STRING);
            query.addScalar("stocks", StandardBasicTypes.INTEGER);
            query.setResultTransformer(Transformers.aliasToBean(Products.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            productList=query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return productList;
    }

    public void deleteProduct(String ids){
        String sql ="UPDATE product p SET p.deletes = 1 WHERE p.id IN ("+ids+")";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    public void recoveryProduct(String ids){
        String sql ="UPDATE product p SET p.deletes = 0 WHERE p.id IN ("+ids+")";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    /**
     * 分页查询
     * @param currentPage 当前页号：现在显示的页数
     * @param pageSize 每页显示的记录条数
     * @return 封闭了分页信息(包括记录集list)的Bean
     * */

    public List<Products> recycleProductList(int offset,String categoryName,String subCategoryName,String colorSizeName,String productName, int length){
        List<Products> productList = null;
        try {
            String  sql="SELECT p.id,p.productName,p.nowPrice,p.sell,c.categoryName,su.subCategoryName,si.colorSizeName,s.stocks";
            String exceptSelectSql =" FROM Product AS p\n" +
                    "LEFT JOIN category AS c ON c.id =p.category\n" +
                    "LEFT JOIN subcategory AS su ON su.id = p.subCategory\n" +
                    "LEFT JOIN colorsize AS si ON si.id =p.colorSizeId\n" +
                    "LEFT JOIN stock AS s ON s.productId = p.id\n" +
                    "where p.deletes=1 ";
            if (categoryName !=null & categoryName !=""){
                exceptSelectSql+=" AND c.categoryName = '"+categoryName+"'";
            }
            if (subCategoryName !=null&subCategoryName!=""){
                exceptSelectSql+=" AND su.subCategoryName = '"+subCategoryName+"'";
            }
            if (colorSizeName !=null &colorSizeName!=""){
                exceptSelectSql+=" AND si.colorSizeName = '"+colorSizeName+"'";
            }
            if (productName!=null & productName!=""){
                exceptSelectSql+=" AND p.productName LIKE '%" + productName + "%'";
            }
            SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
            query.addScalar("id", StandardBasicTypes.INTEGER);
            query.addScalar("productName", StandardBasicTypes.STRING);
            query.addScalar("nowPrice", StandardBasicTypes.LONG);
            query.addScalar("sell", StandardBasicTypes.INTEGER);
            query.addScalar("categoryName", StandardBasicTypes.STRING);
            query.addScalar("colorSizeName", StandardBasicTypes.STRING);
            query.addScalar("stocks", StandardBasicTypes.INTEGER);
            query.setResultTransformer(Transformers.aliasToBean(Products.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            productList=query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return productList;
    }

    public void  sellProduct(String ids){
        String sql ="UPDATE product p SET p.sell = 1 WHERE p.id IN ("+ids+")";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    public void  noSellProduct(String ids){
        String sql="UPDATE product p SET p.sell = 0 WHERE p.id IN ("+ids+")";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    public int getAllRowCount(String categoryName,String subCategoryName,String colorSizeName,String sell,String productName) {
        String  sql="SELECT p.id,p.productName,p.nowPrice,p.sell,c.categoryName,su.subCategoryName,si.colorSizeName,s.stocks";
        String exceptSelectSql =" FROM Product AS p\n" +
                "LEFT JOIN category AS c ON c.id =p.category\n" +
                "LEFT JOIN subcategory AS su ON su.id = p.subCategory\n" +
                "LEFT JOIN colorsize AS si ON si.id =p.colorSizeId\n" +
                "LEFT JOIN stock AS s ON s.productId = p.id\n" +
                "where p.deletes=0 ";
        if (categoryName !=null & categoryName !=""){
            exceptSelectSql+=" AND c.categoryName = '"+categoryName+"'";
        }
        if (subCategoryName !=null&subCategoryName!=""){
            exceptSelectSql+=" AND su.subCategoryName = '"+subCategoryName+"'";
        }
        if (colorSizeName !=null &colorSizeName!=""){
            exceptSelectSql+=" AND si.colorSizeName = '"+colorSizeName+"'";
        }
        if (sell != null&sell!=""){
            int s = Integer.valueOf(sell);
            exceptSelectSql+=" AND p.sell = '"+ s +"'";
        }
        if (productName!=null & productName!=""){
            exceptSelectSql+=" AND p.productName LIKE '%" + productName + "%'";
        }

        return getCurrentSession().createSQLQuery(sql+exceptSelectSql).list().size();
    }

    public int getRecycleAllRowCount(String categoryName,String subCategoryName,String colorSizeName,String productName) {
        String  sql="SELECT p.id,p.productName,p.nowPrice,p.sell,c.categoryName,su.subCategoryName,si.colorSizeName,s.stocks";
        String exceptSelectSql =" FROM Product AS p\n" +
                "LEFT JOIN category AS c ON c.id =p.category\n" +
                "LEFT JOIN subcategory AS su ON su.id = p.subCategory\n" +
                "LEFT JOIN colorsize AS si ON si.id =p.colorSizeId\n" +
                "LEFT JOIN stock AS s ON s.productId = p.id\n" +
                "where p.deletes=1 ";
        if (categoryName !=null & categoryName !=""){
            exceptSelectSql+=" AND c.categoryName = '"+categoryName+"'";
        }
        if (subCategoryName !=null&subCategoryName!=""){
            exceptSelectSql+=" AND su.subCategoryName = '"+subCategoryName+"'";
        }
        if (colorSizeName !=null &colorSizeName!=""){
            exceptSelectSql+=" AND si.colorSizeName = '"+colorSizeName+"'";
        }
        if (productName!=null & productName!=""){
            exceptSelectSql+=" AND p.productName LIKE '%" + productName + "%'";
        }
        return getCurrentSession().createSQLQuery(sql+exceptSelectSql).list().size();
    }

    public List<ProductsDetails> displayProductDetails(int id){
        List<ProductsDetails> productsDetailsList =null;
        String sql="SELECT\n" +
                "p.id,\n" +
                "p.productName,\n" +
                "p.originalPrice,\n" +
                "p.nowPrice,\n" +
                "p.cost,\n" +
                "p.carousel0,\n" +
                "p.carousel1,\n" +
                "p.carousel2,\n" +
                "p.carousel3,\n" +
                "p.carousel4,\n" +
                "p.keyWord,\n" +
                "p.detailDrawing0,\n" +
                "p.detailDrawing1,\n" +
                "p.detailDrawing2,\n" +
                "p.sell,\n" +
                "p.basicsNum,\n" +
                "pd.brand,\n" +
                "pd.productCode,\n" +
                "pd.pendulumType,\n" +
                "pd.collarType,\n" +
                "pd.color,\n" +
                "pd.size,\n" +
                "pd.`year`,\n" +
                "pd.style,\n" +
                "pd.fabric,\n" +
                "pd.sleeve,\n" +
                "pd.age,\n" +
                "pd.scene,\n" +
                "pd.elements,\n" +
                "pd.technology,\n" +
                "pd.object,\n" +
                "pd.waistType,\n" +
                "s.stocks\n" +
                " FROM\n" +
                " product p\n" +
                " LEFT JOIN productdetails AS pd ON pd.productId = p.id\n" +
                " LEFT JOIN stock AS s ON s.productId = p.id WHERE p.id="+id+"\n";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.setResultTransformer(Transformers.aliasToBean(ProductsDetails.class));
        query.addScalar("id",StandardBasicTypes.INTEGER);
        query.addScalar("productName",StandardBasicTypes.STRING);
        query.addScalar("originalPrice",StandardBasicTypes.LONG);
        query.addScalar("nowPrice",StandardBasicTypes.LONG);
        query.addScalar("cost",StandardBasicTypes.LONG);
        query.addScalar("carousel0",StandardBasicTypes.STRING);
        query.addScalar("carousel1",StandardBasicTypes.STRING);
        query.addScalar("carousel2",StandardBasicTypes.STRING);
        query.addScalar("carousel3",StandardBasicTypes.STRING);
        query.addScalar("carousel4",StandardBasicTypes.STRING);
        query.addScalar("keyWord",StandardBasicTypes.STRING);
        query.addScalar("detailDrawing0",StandardBasicTypes.STRING);
        query.addScalar("detailDrawing1",StandardBasicTypes.STRING);
        query.addScalar("detailDrawing2",StandardBasicTypes.STRING);
        query.addScalar("sell",StandardBasicTypes.INTEGER);
        query.addScalar("basicsNum",StandardBasicTypes.LONG);
        query.addScalar("brand",StandardBasicTypes.STRING);
        query.addScalar("productCode",StandardBasicTypes.STRING);
        query.addScalar("pendulumType",StandardBasicTypes.STRING);
        query.addScalar("collarType",StandardBasicTypes.STRING);
        query.addScalar("color",StandardBasicTypes.STRING);
        query.addScalar("size",StandardBasicTypes.STRING);
        query.addScalar("year",StandardBasicTypes.STRING);
        query.addScalar("style",StandardBasicTypes.STRING);
        query.addScalar("fabric",StandardBasicTypes.STRING);
        query.addScalar("sleeve",StandardBasicTypes.STRING);
        query.addScalar("age",StandardBasicTypes.STRING);
        query.addScalar("scene",StandardBasicTypes.STRING);
        query.addScalar("elements",StandardBasicTypes.STRING);
        query.addScalar("technology",StandardBasicTypes.STRING);
        query.addScalar("object",StandardBasicTypes.STRING);
        query.addScalar("waistType",StandardBasicTypes.STRING);
        query.addScalar("stocks",StandardBasicTypes.INTEGER);
        productsDetailsList=query.list();
        return productsDetailsList;
    }

    public Product findProductById(int id){
        String sql="SELECT * FROM Product p WHERE p.id="+id+"";
        Product product=(Product)getCurrentSession().createSQLQuery(sql).addEntity(Product.class).list().get(0);
        return product;
    }

    public void updateProduct(Product product){
        String sql="UPDATE product pr SET pr.basicsNum="+product.getBasicsNum()+" ,pr.carousel0='"+product.getCarousel0()+"' ,pr.carousel1='"+product.getCarousel1()+"' ,pr.carousel2='"+product.getCarousel2()+"' ,pr.carousel3='"+product.getCarousel3()+"',pr.carousel4='"+product.getCarousel4()+"',pr.cost="+product.getCost()+",pr.detailDrawing0='"+product.getDetailDrawing0()+"',pr.detailDrawing1='"+product.getDetailDrawing1()+"',pr.detailDrawing2='"+product.getDetailDrawing2()+"',pr.keyWord='"+product.getKeyWord()+"',pr.nowPrice="+product.getNowPrice()+",pr.originalPrice="+product.getOriginalPrice()+",pr.productName='"+product.getProductName()+"' WHERE pr.id="+product.getId()+"";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    public int getProductAllRowCount(String productName){
        String sql="SELECT * FROM product p";
        String exceptSelectSql="  WHERE p.deletes = 0 AND p.sell = 1";
        if (productName!=null&& productName!=""){
            exceptSelectSql+=" AND p.productName LIKE '%"+productName+"%'";
        }
        return getCurrentSession().createSQLQuery(sql+exceptSelectSql).list().size();
    }

    public List<Product> findFrontEndProductList(int offset,String productName, int length){
        List<Product> productList = null;
        try {
            String  sql="SELECT * FROM product p ";
            String exceptSelectSql="  WHERE p.deletes = 0 AND p.sell = 1";
            if (productName!=null&&productName!=""){
                exceptSelectSql+=" AND p.productName LIKE '%"+productName+"%'";
            }
            SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
            query.addScalar("id",StandardBasicTypes.INTEGER);
            query.addScalar("productName",StandardBasicTypes.STRING);
            query.addScalar("carousel0",StandardBasicTypes.STRING);
            query.addScalar("nowPrice",StandardBasicTypes.LONG);
            query.addScalar("originalPrice",StandardBasicTypes.LONG);
            query.addScalar("cost",StandardBasicTypes.LONG);
            query.addScalar("basicsNum",StandardBasicTypes.LONG);
            query.setResultTransformer(Transformers.aliasToBean(Product.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            productList=query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return productList;

    }

    public List<Comment> findCommentById(int offset,int productId, int length){
        List<Comment> commentList = null;
        try {
            String sql="SELECT * FROM comment c WHERE c.productId ="+productId+"";
            SQLQuery query = getCurrentSession().createSQLQuery(sql);
            query.setResultTransformer(Transformers.aliasToBean(Comment.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            commentList=query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return commentList;
    }

    public List<Comment> findComment(int offset,String userName,int length){
        List<Comment> commentList = null;
        try {
            String sql="SELECT * FROM comment c";
            String exceptSelectSql=" where c.deletes = 0";
            if (userName !=null && userName!=""){
                exceptSelectSql+=" and c.userName LIKE '%"+userName+"%'";
            }
            SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
            query.setResultTransformer(Transformers.aliasToBean(Comment.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            commentList=query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return commentList;
    }


    public List<Comment> findRecoveryComment(int offset,String userName,int length){
        List<Comment> commentList = null;
        try {
            String sql="SELECT * FROM comment c";
            String exceptSelectSql=" where c.deletes = 1";
            if (userName !=null && userName!=""){
                exceptSelectSql+=" and c.userName LIKE '%"+userName+"%'";
            }
            SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
            query.setResultTransformer(Transformers.aliasToBean(Comment.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            commentList=query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return commentList;
    }

    public int getCommetAllRowCount(int productId){
        String sql="SELECT * FROM comment c WHERE c.productId ="+productId+"";
        return getCurrentSession().createSQLQuery(sql).list().size();
    }

    public int getCommentAllRowCount(String userName){
        String sql="SELECT * FROM comment c";
        String exceptSelectSql=" where c.deletes = 0";
        if (userName !=null && userName!=""){
            exceptSelectSql+=" and c.userName LIKE '%"+userName+"%'";
        }
        return getCurrentSession().createSQLQuery(sql+exceptSelectSql).list().size();
    }

    public int getCommentRowCount(String userName){
        String sql="SELECT * FROM comment c";
        String exceptSelectSql=" where c.deletes = 1";
        if (userName !=null && userName!=""){
            exceptSelectSql+=" and c.userName LIKE '%"+userName+"%'";
        }
        return getCurrentSession().createSQLQuery(sql+exceptSelectSql).list().size();
    }

    public void addCollection(Collection collection){
        getCurrentSession().save(collection);
    }

    public List<Collection> findByProductIdAndUserId(int productId,int userId){
        String sql ="SELECT * FROM collection WHERE collection.userId="+userId+" AND  collection.productId="+productId+"";
        List<Collection> collectionList = getCurrentSession().createSQLQuery(sql).addEntity(Collection.class).list();
        return collectionList;
    }

    public void deleteCollection(int id){
        getCurrentSession().createSQLQuery("DELETE FROM collection WHERE collection.id="+id+"").executeUpdate();
    }

    public void deleteComment(String ids){
        getCurrentSession().createSQLQuery("UPDATE comment SET comment.deletes=1 WHERE comment.id IN ("+ids+")").executeUpdate();
    }

    public void recoveryComment(String ids){
        getCurrentSession().createSQLQuery("UPDATE comment SET comment.deletes=0 WHERE comment.id IN ("+ids+")").executeUpdate();
    }
    }
